package o2o.web.actions.order.user;

import com.jinyou.utils.common.*;
import jPush.dao.JPushMessage;
import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import notice.dao.NoticeType;
import o2o.cache.company.postman.PostCompanyMemory;
import o2o.cache.order.OrderInfoMemory;
import o2o.cache.shop.ShopMemory;
import o2o.cache.user.UserInfoMemory;
import o2o.cache.user.UserOnlineImpl;
import o2o.config.dao.UserDaoSql;
import o2o.dao.PO.PojoTables;
import o2o.dao.PO.company.postman.PostCompanyInfo;
import o2o.dao.PO.company.postman.PostmanSignCompanyInfo;
import o2o.dao.PO.order.ORDER_COMMENT_TYPE;
import o2o.dao.PO.order.OrderCommentInfo;
import o2o.dao.PO.order.OrderInfo;
import o2o.company.model.ShopInfo;
import o2o.dao.PO.user.UserInfo;
import o2o.dao.VO.order.OrderCommentManage;
import o2o.dao.VO.order.OrderDetailsVo;
import o2o.system.data.SYS_CONFIG_PATH;
import o2o.web.actions.notice.Notice;
import o2o.web.actions.user.User;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;

import java.io.File;
import java.sql.SQLException;
import java.util.*;

@Controller
public class UserCommentManagermentAction {

    // 管理员查看评论
    @RequestMapping(method = RequestMethod.POST, value = "/admin/managerment/user/comment")
    @ResponseBody
    public String commentManager(String city, String county,
                                 Long startTime, Long endTime,
                                 Integer page, Integer size,
                                 String role, String day,
                                 int reply,
                                 int comment,// 好评差评   2好评 4差评 3中评
                                 //  reply: 是否回复    comment  评价  ： 好评  中评    差评   message： 有无内容  1全部 2 有内容
                                 int message,
                                 String cityAdminUsername, // 代理账号
                                 int commentType, // 1商家  2配送员 3商品
                                 String postmanName, String postmanUsername, String input, Integer isExport) {

        if (ValidateUtil.isNotAbsInteger(page)) {
            page = 1;
        }
        if (ValidateUtil.isNotAbsInteger(size)) {
            size = 20;
        }
        if (ValidateUtil.isNotNull(input)) {
            input = input.replace(" ", "");
        }
//        input = input.replace(" ", "");
        if (!"1".equals(role) && !"2".equals(role)) {
            role = "1";
        }
        if ("1".equals(day)) {
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.DATE, -1);
            Date time = cal.getTime();
            startTime = time.getTime();
            endTime = new Date().getTime();
        }
        if ("7".equals(day)) {
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.DATE, -7);
            Date time = cal.getTime();
            startTime = time.getTime();
            endTime = new Date().getTime();
        }
        if ("30".equals(day)) {
            Calendar cal = Calendar.getInstance();
            cal.add(Calendar.DATE, -30);
            Date time = cal.getTime();
            startTime = time.getTime();
            endTime = new Date().getTime();
        }


        // 得到目前的所有评价
        List<OrderCommentManage> orderCommentList = getComment(startTime, endTime, cityAdminUsername, page, size, city, county, input, role, commentType, postmanName, postmanUsername);


        //  对数据进行筛选
        if (orderCommentList.size() > 0) {
            orderCommentList = repeat(orderCommentList);
        }

        //  对数据进行评价处理
        //   List<OrderCommentManage> orderCommentManageList = commentHandler(orderCommentList);
        // 对数据进行处理
        List<OrderCommentManage> returnList = messageHandler(orderCommentList, reply, comment, message);
        if (ValidateUtil.isAbsList(returnList)) {
            JYPageUtils.PageList pageList = JYPageUtils.getList(returnList, page, size);
            //  导出
            if (null != isExport && isExport - 1 == 0) {
                String downloadUrl = exportExcel(returnList);
                if (ValidateUtil.isNotNull(downloadUrl)) {
                    if(ValidateUtil.isNotNetUrl(downloadUrl)){
                        downloadUrl = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                    }
                    return AjaxResult.successResult(downloadUrl);
                }
            }
            return AjaxResult.successResult(pageList.totalCount, pageList.size, pageList.list);
        }
        return AjaxResult.successResult(0, 0, null);
    }

    // 把数据整合
    private List<OrderCommentManage> repeat(List<OrderCommentManage> orderCommentList) {
        List<OrderCommentManage> list = new ArrayList<>();
        // 把重复的订单分离出来
        for (int i = 0; i < orderCommentList.size(); i++) {
            for (int j = i + 1; j < orderCommentList.size(); j++) {
                if (orderCommentList.get(i).getOrderNo().equals(orderCommentList.get(j).getOrderNo())) {
                    list.add(orderCommentList.get(j));
                    orderCommentList.remove(j);
                    j--;
                }
            }
        }

        // 把重复的订单整合到一起
        for (OrderCommentManage commentManage : orderCommentList) {
            String comm = "";
            if (commentManage.getCommentType() == ORDER_COMMENT_TYPE.GOODS_COMMENT) {   //  商品
                commentManage.setGoodsStar(commentManage.getStar());
                commentManage.setStar(0);
                commentManage.setPsStar(0);
            }
            if (commentManage.getCommentType() == ORDER_COMMENT_TYPE.POSTMAN_COMMENT) {   // 配送员
                commentManage.setPsStar(commentManage.getStar());
                commentManage.setStar(0);
                commentManage.setGoodsStar(0);
            }
            if (commentManage.getCommentType() == ORDER_COMMENT_TYPE.SHOP_COMMENT) {   //  店铺
                commentManage.setPsStar(0);
                commentManage.setGoodsStar(0);
            }
            for (int i = 0; i < list.size(); i++) {
                if (commentManage.getOrderNo().equals(list.get(i).getOrderNo())) {
                    if (list.get(i).getCommentType() == ORDER_COMMENT_TYPE.GOODS_COMMENT) {   //  商品
                        commentManage.setGoodsStar(list.get(i).getStar());
                    }
                    if (list.get(i).getCommentType() == ORDER_COMMENT_TYPE.POSTMAN_COMMENT) {   // 配送员
                        commentManage.setPsStar(list.get(i).getStar());
                    }
                    if (list.get(i).getCommentType() == ORDER_COMMENT_TYPE.SHOP_COMMENT) {   //  店铺
                        commentManage.setStar(list.get(i).getStar());
                    }
                    if (list.get(i).getComment() != null && !"".equals(list.get(i).getComment())) {
                        commentManage.setComment(list.get(i).getComment());
                    }
                    list.remove(i);
                    i--;
                }
            }
        }
        return orderCommentList;
    }

    //  对数据进行处理                     reply: 是否回复    comment  评价  ： 好评  中评    差评   message： 有无内容  1全部 2 有内容
    private List<OrderCommentManage> messageHandler(List<OrderCommentManage> orderCommentManageList, int reply, int comment, int message) {
        // 用于返回的集合
        List<OrderCommentManage> returnList = new ArrayList<>();

        if (reply == 1) {
            returnList = orderCommentManageList;
        } else if (reply == 2) {
            for (OrderCommentManage orderCommentManage : orderCommentManageList) {
                if (orderCommentManage.getReplyComment() == null || "".equals(orderCommentManage.getReplyComment())) {
                    returnList.add(orderCommentManage);
                }
            }
        } else if (reply == 3) {
            for (OrderCommentManage orderCommentManage : orderCommentManageList) {
                if (orderCommentManage.getReplyComment() != null && !"".equals(orderCommentManage.getReplyComment())) {
                    returnList.add(orderCommentManage);
                }
            }
        }
        //  =============  以上对回复状态进行筛选
        if (comment == 2) {// 好评
            for (int i = 0; i < returnList.size(); i++) {
//                if (returnList.get(i).getStar() != 5) {
                if (returnList.get(i).getStar() != 5 && returnList.get(i).getPsStar() != 5) {
                    returnList.remove(i);
                    i--;
                }
            }
        } else if (comment == 4) {// 差评
            for (int i = 0; i < returnList.size(); i++) {
//                if (returnList.get(i).getStar() != 1) {
                if (returnList.get(i).getStar() != 1 && returnList.get(i).getPsStar() != 1) {
                    returnList.remove(i);
                    i--;
                }
            }
        } else if (comment == 3) {// 中评
            for (int i = 0; i < returnList.size(); i++) {
//                if (returnList.get(i).getStar() == 1 || returnList.get(i).getStar() == 5) {
                if ((returnList.get(i).getStar() == 1 || returnList.get(i).getStar() == 5 || returnList.get(i).getStar() == 0) && (returnList.get(i).getPsStar() == 1 || returnList.get(i).getPsStar() == 5)) {
                    returnList.remove(i);
                    i--;
                }
            }
        }
        // =============  以上对满意度进行筛选
        if (message == 2) {
            for (int i = 0; i < returnList.size(); i++) {
                if (returnList.get(i).getComment() == null || "".equals(returnList.get(i).getComment().replace(" ", ""))) {
                    returnList.remove(i);
                    i--;
                }
            }
        }

        return returnList;
    }


    //  得到数据
    private List<OrderCommentManage> getComment(Long startTime, Long endTime, String cityAdminUsername, Integer page, Integer size, String city, String county, String input, String role, Integer commentType, String postmanName, String postmanUsername) {
        /*SELECT c.id,s.shopname,c.star,c.comment, c.orderno ,c.commenttype ,c.objid,c.replyComment,c.replytime FROM
        order_comment c,order_info o,shop_info s WHERE o.orderno=c.orderno AND o.shopid=s.id AND c.delflag=0 AND o.delflag=0 AND s.delflag=0
        AND c.createtime>=1514736000000 AND c.createtime<=1547827199000 AND o.city='沧州'  AND ( s.shopname LIKE '%5%'
           OR c.star=5 ) GROUP BY c.id
     */
        String time = "";
        if (startTime != null && endTime != null) {
            time = " AND c.createtime BETWEEN " + startTime + " and " + endTime;
        }
        String citys = "";
        if (StringUtils.isNotEmpty(city)) {
            citys = " AND cp.city='" + city + "'";
        }
        String countys = "";
        if (StringUtils.isNotEmpty(county)) {
            countys = " AND cp.county='" + county + "'";
        }
        List<OrderCommentManage> orderCommentManageList = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT c.id,o.shopname,c.star,c.comment,c.createTime, c.orderno ,c.commenttype ,")
                .append("c.objid,c.replyComment,c.replytime, o.postmanName,o.postmanUsername, g.name AS goodsComm FROM (")
                .append(PojoTables.ORDER_COMMENT).append(" c,").append(PojoTables.OrderInfo).append(" o)")
                .append(" LEFT JOIN goods_info g ON g.id=c.objid ")
                .append(" left join ").append(PojoTables.OrderInfo).append(" ori on ori.orderNo=c.orderNo and ori.delFlag=0 ")
                .append(" left join ").append(PojoTables.ShopInfo).append(" si on si.id=ori.shopId")
                .append(" left join ").append(PojoTables.USER_AGENT_RELATION).append(" uar on uar.agentId=ori.agentId and uar.delFlag=0 ")
                .append(" left join ").append(PojoTables.CityPostman).append(" cp on cp.username=o.postmanUsername")
                .append(" WHERE o.orderno=c.orderno ")
                .append(" AND c.delflag=0 AND o.delflag=0 and si.delFlag=0 and ori.isRefundApply=0 and ori.isPartCancelApply=0 and ori.orderStatus=9 ");
        if (commentType != 0) {
            sb.append(" AND c.commentType=").append(commentType);
        }
        if (null != cityAdminUsername && ValidateUtil.isNotNull(cityAdminUsername)) {
            sb.append(" and uar.username = '").append(cityAdminUsername).append("' ");
        }
        sb.append(time).append(citys).append(countys).append("    ");
        if (ValidateUtil.isNotNull(input) && !("UNDEFINED".equals(input)) && !"".equals(input.replace(" ", "")) && !("undefined".equals(input))) {
            char c = input.charAt(0);
            if (c <= '5' && c >= '1') {
                sb.append(" AND ( o.shopname LIKE '%").append(input).append("%'").append(" OR c.star=").append(Integer.valueOf(input)).append(" ) GROUP BY c.id");
            } else {
                sb.append(" AND o.shopname LIKE '%").append(input).append("%'");
            }
        }
        if (ValidateUtil.isNotNull(postmanName)) {
            sb.append(" AND o.postmanName LIKE '%").append(postmanName).append("%'");
        }
        if (ValidateUtil.isNotNull(postmanUsername)) {
            sb.append(" AND o.postmanUsername = ").append(postmanUsername);
        }
        try {
            orderCommentManageList = (List<OrderCommentManage>) UserDaoSql.getInstance().list(OrderCommentManage.class, String.valueOf(sb));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return orderCommentManageList;
    }


//    ====================================================================================================================

    // 根据订单号查订单
    private List<OrderDetailsVo> getOrder(String orderNo) {
        List<OrderDetailsVo> orderDetailsVoList = new ArrayList<>();
        StringBuffer sb = new StringBuffer();
        /*  SELECT o.orderno,g.Price AS goodsPrice,s.shopname,g.name,o.totalcount,g.descs,g.imageUrl,o.totalprice,of.buyer,of.telephone,of.address,s.address,s.telephone,of.note
                FROM order_goods o,shop_info s,goods_info g ,order_info of
                WHERE o.orderno=1901051229426280 AND s.id=o.shopid AND o.goodsid=g.id AND of.orderno=o.orderno
            */
        sb.append("SELECT o.orderno,g.Price AS goodsPrice,s.shopname,g.name,o.totalcount,g.descs,g.imageUrl,o.totalprice,of.buyer,of.telephone AS userTelephone,of.address AS userAddress,s.address AS shopAddress,s.telephone AS shopTelephone,of.note,of.createtime,of.orderstatus FROM ").append(PojoTables.OrderGoods).append(" o,").append(PojoTables.ShopInfo).append(" s,")
                .append(PojoTables.GoodsInfo).append(" g,").append(PojoTables.OrderInfo).append(" of WHERE o.orderno=").append(orderNo).append(" AND s.id=o.shopid AND o.goodsid=g.id AND of.orderno=o.orderno");
        try {
            orderDetailsVoList = (List<OrderDetailsVo>) UserDaoSql.getInstance().list(OrderDetailsVo.class, String.valueOf(sb));
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return orderDetailsVoList;
    }

    // 根据commentid获取评论
    private List<OrderCommentManage> getCommentById(String orderNo) {

        List<OrderCommentManage> orderCommentInfoList = new ArrayList<>();
        /* SELECT orderno,COMMENT,replyComment FROM order_comment WHERE orderno=1901061351125913 ORDER BY createtime */
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT orderno,COMMENT,username,replyComment FROM ").append(PojoTables.ORDER_COMMENT).append(" WHERE orderno=").append(orderNo).append(" Order By createtime");
        try {
            orderCommentInfoList = (List<OrderCommentManage>) UserDaoSql.getInstance().list(OrderCommentManage.class, String.valueOf(sb));
        } catch (SQLException e) {
            e.getErrorCode();
        }
        return orderCommentInfoList;
    }

    // 给用户回复消息
    @RequestMapping(method = RequestMethod.POST, value = "/admin/reply/message")
    @ResponseBody
    public String replyMes(String rep, String orderNo, String token, String username) {
        //验证用户有效性
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }

        //  更新数据库回复信息
        // 信息封装
        OrderCommentManage orderCommentManage = new OrderCommentManage();
        orderCommentManage.setReplyName(user.getName());
        orderCommentManage.setOrderNo(orderNo);
        orderCommentManage.setReplyUsername(user.getUsername());
        orderCommentManage.setReplyComment(rep);
        orderCommentManage.setUpdateTime(new Date().getTime());
        insert(orderCommentManage, orderNo);
        // 发送提醒
        Notice.publishNotice2Person(null, username, JPushMessage.USER, NoticeType.system, "消息提醒", "店家给您回复了");
        return AjaxResult.successResult(orderNo);
    }

    //  把回复的内容更新数据库
    private void insert(OrderCommentManage orderCommentManage, String orderNo) {
        StringBuffer sb = new StringBuffer();
        /*
UPDATE order_comment SET replyname='' WHERE orderNo=1901061351125913 */
        sb.append("UPDATE ").append(PojoTables.ORDER_COMMENT).append(" SET replyName='").append(orderCommentManage.getReplyName()).append("',replyusername='")
                .append(orderCommentManage.getReplyUsername()).append("',replycomment='").append(orderCommentManage.getReplyComment()).append("'")
                .append(" WHERE orderno=").append(orderNo);
        try {
            UserDaoSql.getInstance().executeBySql(String.valueOf(sb));
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // 获取对商家的评论 通过的order_comment  表进行查询的
    @RequestMapping(value = "/admin/list/shop/comment", method = RequestMethod.POST)
    @ResponseBody
    public String list_comment_shop(String token, Long shopId, String city, String county, Integer shopRole, Long startTime, Long endTime,
                                    Integer reply, Integer comment, Integer message, Integer day, String key, Integer page, Integer size) {
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        if (!User.hasAdminAuth(user.getUserType())) {
            return AjaxResult.successResult("您没有访问权限");
        }
        if (ValidateUtil.isAbsLong(startTime) || ValidateUtil.isAbsLong(endTime)) {
            if (day != null && day > 0) {
                endTime = DateUtils.getCurrTime();
                startTime = DateUtils.getStartTime(new Date(), day);
            }
        }

        StringBuilder sql = new StringBuilder();
        sql.append("select SI.shopName as shopName," +
                "OC.star as star ," +
                "OC.id as Id," +
                "OC.orderNo as orderNo," +
                "OC.comment as comment, " +
                "IfNull(OC.replyComment,'') as replyComment " +
                " from ").append(PojoTables.ORDER_COMMENT).append(" AS OC ")
                .append(" LEFT JOIN  ").append(PojoTables.ShopInfo).append(" AS SI ")
                .append("       ON OC.objId = SI.id ")
                .append(" LEFT JOIN ").append(PojoTables.OrderInfo).append(" AS OI")
                .append("       ON OC.orderNo = OI.orderNo")
                .append(" WHERE OC.delFlag = 0 ")
                .append(" AND OC.commentType = 3 ")
        ;
        // 城市
        if (StringUtils.isNotEmpty(city)) {
            sql.append(" and (").append(" OI.city LIKE '%" + city + "+%'").append(" or ").append("OI.shopCity like '" + city + "'").append(")");
        }
        // 区域
        if (StringUtils.isNotEmpty(county)) {
            sql.append(" and OI.county like '").append(county).append("'");
        }
        if (ValidateUtil.isID(shopId)) {
            sql.append(" and OC.objId=").append(shopId);
        }
        // 直营店还是加盟店
        if (shopRole != null) {
            sql.append(" and  SI.shopRole =").append(shopRole);
        }
        // 开始时间
        if (startTime != null && startTime > 0) {
            sql.append(" and  OC.createTime >").append(startTime);
        }
        // 结束时间
        if (endTime != null && endTime > 0) {
            sql.append(" and  OC.createTime <").append(endTime);
        }
        // 有无内容
        if (message == null || message == 0) {

        } else if (message != null && message == 1) {
            // 有内容
            sql.append(" and (OC.comment is not null and  OC.comment !='')");
        } else if (message != null && message == 2) {
            // 无内容
            sql.append(" and (OC.comment is null or OC.comment ='')");
        }
        // 满意度 1 好评 2 中评 3 差评 0 全部

        if (comment == null || comment == 0) {

        } else if (comment == 1) {
            sql.append(" and OC.star >").append("3");
        } else if (comment == 2) {
            sql.append(" and OC.star =").append("3");
        } else if (comment == 3) {
            sql.append(" and OC.star <").append("3");
        }
        // 是否回复 0  全部  1 回复 2 未回复
        if (reply == null || reply == 0) {

        } else if (reply == 1) {
            sql.append(" and OC.replyComment is not null ");
        } else if (reply == 2) {
            sql.append(" and OC.replyComment is null ");
        }
        if (StringUtils.isNotEmpty(key)) {
            sql.append(" and OC.comment like '%").append(key).append("%'");
        }
        List<HashMap<String, Object>> maps = null;
        try {
            maps = UserDaoSql.getInstance().queryBySql(sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        if (page == null || page == 0) {
            page = 1;
        }
        if (size == null || size == 0) {
            size = 20;
        }
        if (maps == null || maps.size() == 0) {
            return AjaxResult.successResult(0, 0, null);
        }
        JYPageUtils.PageList pageList = JYPageUtils.getList(maps, page, size);
        return AjaxResult.successResult(pageList.totalCount, pageList.size, pageList.list);
    }

    // ===================
    // 导excel
    private String exportExcel(List<OrderCommentManage> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);

            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);
            WritableSheet sheet = workbook.createSheet("sheet1", 0);

            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式

            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "商家"));
            sheet.addCell(new Label(col++, row, "配送员账号"));
            sheet.addCell(new Label(col++, row, "配送员姓名"));
            sheet.addCell(new Label(col++, row, "配送评分"));
            // sheet.addCell(new Label(col++, row, "评价内容"));
            // sheet.addCell(new Label(col++, row, "回复状态"));

            row = 1;
            for (OrderCommentManage info : list) {
                col = 0;
                sheet.addCell(new Label(col++, row, info.getShopName() + ""));
                sheet.addCell(new Label(col++, row, info.getPostmanUsername() + ""));
                sheet.addCell(new Label(col++, row, info.getPostmanName() + ""));
                sheet.addCell(new Label(col++, row, info.getPsStar() == 0 ? "0" : info.getPsStar() + ""));
                // sheet.addCell(new Label(col++, row, info.getComment() + ""));
                // sheet.addCell(new Label(col++, row, "".equals(info.getReplyComment()) ? "未回复" : "已回复" + ""));
                row++;     // 行
            }

            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            outputPath = JYFileUtils.saveFile2Oss(outputPath);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }


    @RequestMapping(method = RequestMethod.POST, value = "/admin/comment/postman/list")
    @ResponseBody
    public String list_postman_comment(String token, String postman, String city, String county,
                                       Long startTime, Long endTime,
                                       Integer comment,
                                       Integer isExport,
                                       @RequestParam(defaultValue = "1") Integer page,
                                       @RequestParam(defaultValue = "20") Integer size) {
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        StringBuilder sql = new StringBuilder();
        sql.append(" from ").append(PojoTables.ORDER_COMMENT).append(" as OC")
                .append(" inner join ").append(PojoTables.OrderInfo).append(" as OI")
                .append(" ON OC.orderNo = OI.orderNo and OI.delFlag = 0 and OC.delFlag = 0")
                .append(" inner join ").append(PojoTables.CityPostman).append(" as CP")
                .append(" on OC.objUsername = CP.username").append(" and CP.delFlag = 0")
                .append(" where  OC.commentType = ").append(ORDER_COMMENT_TYPE.POSTMAN_COMMENT);
        if (ValidateUtil.isAbsLong(startTime)) {
            sql.append(" and OC.createTime > ").append(startTime);
        }
        if (ValidateUtil.isAbsLong(endTime)) {
            sql.append(" and OC.createTime < ").append(endTime);
        }
        if (comment != null) {
            // 1 满意 2 中 3 差评
            if (comment == 1) {
                sql.append(" and OC.star >").append(3);
            } else if (comment == 2) {
                sql.append(" and OC.star =").append(3);
            } else if (comment == 3) {
                sql.append(" and OC.star <").append(3);
            }
        }
        if (StringUtils.isNotBlank(postman)) {
            sql.append(" and OC.objUsername like '%").append(postman).append("%' ");
        }
        if (StringUtils.isNotBlank(city)) {
            sql.append(" and (OI.shopCity ='").append(city).append("' or OI.city ='").append(city).append("' )");
        }
        if (StringUtils.isNotBlank(county)) {
            sql.append(" and OI.county like '%").append(county).append("%' ");
        }
        StringBuilder countSql = new StringBuilder();
        countSql.append(" select count(OC.id) as counts  ")
                .append(sql.toString());
        StringBuilder selectSql = new StringBuilder();
        selectSql.append(" select  OC.objUsername as objUsername,OC.orderNo as orderNo,OC.star as star, OC.comment,OC.createTime ")
                .append(sql.toString());
        if (null == isExport || isExport != 1) {
            sql.append(" limit ").append((page - 1) * size).append(",").append(size);
        }
        long count = 0;
        List<HashMap<String, Object>> dataList = null;
        try {
            count = UserDaoSql.getInstance().getCount(countSql.toString());
            dataList = UserDaoSql.getInstance().queryBySql(selectSql.toString());
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.errorResult("获取失败,请稍后重试");
        }
        if (dataList != null && dataList.size() > 0) {
            for (HashMap<String, Object> map : dataList) {
                UserInfo tempUser = UserInfoMemory.getInstance().getUserInfo(map.get("objUsername") == null ? "" : map.get("objUsername").toString());
                map.put("objName", tempUser.getName());
            }
            if (null != isExport && isExport - 1 == 0) {
                String downloadUrl = exportPostmanExcel(dataList);
                if (ValidateUtil.isNotNull(downloadUrl)) {
                    String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                    return AjaxResult.successResult(url);
                }
            }
            return AjaxResult.successResult(dataList, count);
        }
        return AjaxResult.successResult(null, count);
    }

    // 导excel
    private String exportPostmanExcel(List<HashMap<String, Object>> list) {
        if (!ValidateUtil.isAbsList(list)) {
            return null;
        }
        String outputPath = "";
        try {
            outputPath = SYS_CONFIG_PATH.ORDER_IMAGE + "/" + DateUtils.getCurrentYearMonth() + "/" + DateUtils.getCurrTime() + ".xls";
            File outputFile = new File(SYS_CONFIG_PATH.IMAGE_BASE_FILE + outputPath);
            FileUtils.forceMkdirParent(outputFile);
            WritableWorkbook workbook = Workbook.createWorkbook(outputFile);
            WritableSheet sheet = workbook.createSheet("sheet1", 0);
            jxl.write.NumberFormat nf = new jxl.write.NumberFormat("#0.00");   // 设置数字格式
            jxl.write.WritableCellFormat wcfN = new jxl.write.WritableCellFormat(nf); // 设置表单格式
            int col = 0;   // 列
            int row = 0;  // 行
            sheet.addCell(new Label(col++, row, "订单编号"));
            sheet.addCell(new Label(col++, row, "配送员账号"));
            sheet.addCell(new Label(col++, row, "配送员姓名"));
            sheet.addCell(new Label(col++, row, "配送满意度"));
            sheet.addCell(new Label(col++, row, "评价内容"));

            row = 1;
            for (HashMap<String, Object> info : list) {
                col = 0;
                sheet.addCell(new Label(col++, row, info.get("orderNo") + ""));
                sheet.addCell(new Label(col++, row, info.get("objUsername") + ""));
                sheet.addCell(new Label(col++, row, ValidateUtil.isNull(info.get("objName")) ? "" : info.get("objName").toString()));
                int star = 0;
                star = Integer.parseInt(ValidateUtil.isNull(info.get("star")) ? "0" : info.get("star").toString());
                if (star > 3) {
                    sheet.addCell(new Label(col++, row, "满意"));
                } else if (star == 3) {
                    sheet.addCell(new Label(col++, row, "一般"));
                } else {
                    sheet.addCell(new Label(col++, row, "不满意"));
                }
                sheet.addCell(new Label(col++, row, ValidateUtil.isNull(info.get("comment")) ? "" : info.get("comment").toString()));
                row++;     // 行
            }
            workbook.write();
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return outputPath;
    }


    // todo: 配送公司-查看骑手的评论
    @RequestMapping(value = "/post/company/order/comment/list")
    @ResponseBody
    public String post_company_order_comment_list(String token, Long companyId, String username, Long startTime, Long endTime, Integer page, Integer size, Integer isExport, Integer comment) {
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(UserOnlineImpl.getInstance().get(token));
        if (user == null) {
            return AjaxResult.errorLogoutResult();
        }
        PostCompanyInfo companyInfo = null;
        if (ValidateUtil.isNotID(companyId)) {
            companyInfo = PostCompanyMemory.getInstance().getByUsername(user.getUsername());
        } else {
            companyInfo = PostCompanyMemory.getInstance().getById(companyId);
        }
        if (companyInfo == null) {
            return AjaxResult.errorResult("未获取到配送公司信息");
        }
        companyId = companyInfo.getId();
        if (page == null) {
            page = 1;
        }
        if (size == null) {
            size = 10;
        }
        /**
         * SELECT
         *   b.*
         * FROM
         *   order_comment b
         *   LEFT JOIN
         *     (SELECT DISTINCT
         *       username AS postman
         *     FROM
         *       postman_sign_company
         *     WHERE companyId = 1
         *       AND STATUS = 9  AND delFlag = 0) a
         *     ON b.objUsername = a.postman
         *   LEFT JOIN order_info o
         *     ON o.`orderNo` = b.`orderNo`
         * WHERE b.delFlag = 0
         *   AND b.commentType = 2
         *   AND o.`grabCompanyId` = 1
         * ORDER BY b.id DESC ;
         */
        // 查询
        StringBuffer sql = new StringBuffer();
        sql
                .append(" SELECT ")
                .append("    b.* ")
                .append(" FROM ")
                .append(PojoTables.ORDER_COMMENT + "    b ")
                .append(" LEFT JOIN ")
                .append("    (SELECT DISTINCT ")
                .append("       username AS postman ")
                .append("       FROM ")
                .append("           " + PojoTables.POSTMAN_SIGN_COMPANY + " ")
                .append("       WHERE companyId = ").append(companyId)
                .append("       AND STATUS = " + PostmanSignCompanyInfo.POSTMAN_COMPANY_SIGN_STATUS_AGREE + "  AND delFlag = 0) a ")
                .append("   ON b.objUsername = a.postman ")
                .append(" LEFT JOIN " + PojoTables.OrderInfo + " o ")
                .append("   ON o.`orderNo` = b.`orderNo`")
                .append("  WHERE b.delFlag = 0 ")
                .append("  AND b.commentType = ").append(ORDER_COMMENT_TYPE.POSTMAN_COMMENT)
                .append("  AND o.`grabCompanyId` = ").append(companyId);
        if (comment != null) {
            // 1 满意 2 中 3 差评
            if (comment == 1) {
                sql.append(" and b.star >").append(3);
            } else if (comment == 2) {
                sql.append(" and b.star =").append(3);
            } else if (comment == 3) {
                sql.append(" and b.star <").append(3);
            }
        }
        if (ValidateUtil.isAbsLong(startTime)) {
            sql
                    .append("  AND b.createTime >= ").append(startTime);
        }
        if (ValidateUtil.isAbsLong(startTime)) {
            sql
                    .append("  AND b.createTime <= ").append(endTime);
        }
        if (ValidateUtil.isNotNull(username)) {
            sql
                    .append("  AND b.objUsername  like '%").append(username).append("%' ");
        }
        sql
                .append("  ORDER BY b.id DESC ");
        List<OrderCommentInfo> list = null;
        try {
            list = (List<OrderCommentInfo>) UserDaoSql.getInstance().list(OrderCommentInfo.class, sql.toString());
        } catch (Exception e) {
            e.printStackTrace();
            return AjaxResult.errorResult(e.getMessage());
        }
        List<OrderCommentManage> voList = new ArrayList<>();
        if (ValidateUtil.isAbsList(list)) {
            for (OrderCommentInfo info : list) {
                OrderCommentManage converTo = this.converTo(info);
                if (converTo == null) {
                    continue;
                }
                voList.add(converTo);
            }
        }
        if (isExport != null && isExport == 1) {
            String downloadUrl = exportExcel(voList);
            if (ValidateUtil.isNotNull(downloadUrl)) {
                String url = SYS_CONFIG_PATH.IMAGE_BASE_URL + downloadUrl;
                return AjaxResult.successResult(url);
            }
        } else {
            JYPageUtils.PageList pageList = JYPageUtils.getList(voList, page, size);
            if (pageList != null && ValidateUtil.isAbsList(pageList.list)) {
                return AjaxResult.successResult(pageList.list, pageList.totalCount);
            }
        }
        return AjaxResult.successResult(null, 0);
    }

    // 转成
    private OrderCommentManage converTo(OrderCommentInfo info) {
        if (info == null) {
            return null;
        }
        OrderCommentManage manage = new OrderCommentManage();
        manage.setId(info.getId());
        manage.setPsStar(info.getStar());
        manage.setStar(info.getStar());
        manage.setPostmanUsername(info.getObjUsername());
        UserInfo user = UserInfoMemory.getInstance().getUserInfo(info.getObjUsername());
        if (user != null) {
            manage.setPostmanName(user.getName());
        }
        OrderInfo orderInfo = OrderInfoMemory.getInstance().getOrderInfo(info.getOrderNo());
        if (orderInfo != null && (ValidateUtil.isID(orderInfo.getShopId()) || ValidateUtil.isID(orderInfo.getShopId()))) {
            ShopInfo tempShop = ShopMemory.getInstance().getShopById(ValidateUtil.isID(orderInfo.getSquareShopId()) ? orderInfo.getSquareShopId() : orderInfo.getShopId());
            if (tempShop != null) {
                manage.setShopName(tempShop.getShopName());
            }
        }
        manage.setOrderNo(info.getOrderNo());
        manage.setComment(info.getComment());
        manage.setCommentType(info.getCommentType());
        return manage;
    }
}
